package com.javaweb.dao.impl;

import com.javaweb.dao.ArticleDao;
import com.javaweb.model.Article;
import com.javaweb.model.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;
import org.springframework.util.ObjectUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc:
 * @author: Ping
 * @date: 2024/10/9 9:51
 */
@Repository
public class ArticleDaoImpl implements ArticleDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final RowMapper<Article> articleRowMapper = new RowMapper<Article>() {
        @Override
        public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setStatus(rs.getInt("status"));
            article.setDescription(rs.getString("description"));
            article.setCategoryId(rs.getInt("category_id"));
            article.setCreateTime(rs.getDate("create_time"));
            article.setUpdateTime(rs.getDate("update_time"));
            return article;
        }
    } ;

    //    （1）新增文章:
    @Override
    public int insert(Article article) {
        if(ObjectUtils.isEmpty(article.getUserId()) ||
                ObjectUtils.isEmpty(article.getTitle()) || ObjectUtils.isEmpty(article.getContent())||
                ObjectUtils.isEmpty(article.getCategoryId())){
            return 0;
        }
        String sql="insert into tb_article(user_id,title,content,status,description,category_id,create_time,update_time) values(?,?,?,?,?,?,?,?)";
        Object[] args=new Object[]{
                article.getUserId(),
                article.getTitle(),
                article.getContent(),
                article.getStatus(),
                article.getDescription(),
                article.getCategoryId(),
                article.getCreateTime(),
                article.getUpdateTime()
        };
        return jdbcTemplate.update(sql, args) >0?1:0;
    }
    //    （2）更新文章内容：
    @Override
    public int updateContent(Integer id, String newContent) {
        if(ObjectUtils.isEmpty(id) || ObjectUtils.isEmpty(newContent)){
            return 0;
        }
        String sql="update tb_article set content=?,update_time=NOW() where id=?";
        Object[] args=new Object[]{newContent,id};
        return jdbcTemplate.update(sql, args)>0?1:0;
    }
    //    （3）更新文章标题：
    @Override
    public int updateTtile(Integer id, String newTtile) {
        if(ObjectUtils.isEmpty(id) || ObjectUtils.isEmpty(newTtile)){
            return 0;
        }
        String sql="update tb_article set title=?,update_time=NOW() where id=?";
        Object[] args=new Object[]{newTtile,id};
        return jdbcTemplate.update(sql, args)>0?1:0;
    }
    //    （4）删除文章：
    @Override
    public int deleteById(Integer id) {
        if(ObjectUtils.isEmpty(id)){
            return 0;
        }
        String sql="delete from tb_article where id=?";
        Object[] args=new Object[]{id};
        return jdbcTemplate.update(sql, args)>0?1:0;
    }
    //    （5）按照用户id查询文章：
    @Override
    public List<Article> selectByUserId(Integer userId) {
        if(ObjectUtils.isEmpty(userId)){
            return null;
        }
        String sql="select * from tb_article  where user_id=? order by create_time desc";
//        String sql="select * from tb_article a join tb_comment c on a.id=c.article_id  and a.user_id= c.user_id where a.user_id='' order by a.create_time desc";
        Object[] args=new Object[]{userId};
        List<Article> articles=jdbcTemplate.query(sql,args,articleRowMapper);
        return articles.isEmpty()?null:articles;
    }
    //    （6）按照文章titile或内容的关键词模糊查询：
    @Override
    public List<Article> selectByKeyword(String keyword) {
        if(ObjectUtils.isEmpty(keyword)){
            return null;
        }
        String sql="select * from tb_article where title like ? or content like ? order by create_time desc";
        List<Article> articles=jdbcTemplate.query(sql,new Object[]{"%"+keyword+"%","%"+keyword+"%"},articleRowMapper);
        return articles.isEmpty()?null:articles;
    }
    //    （7）按照文章的创建时间的时间区间进行查询：
    @Override
    public List<Article> selectByTimePeriod(Date startTime, Date endTime) {
        String sql = "SELECT * FROM tb_article " +
                "WHERE (create_time >= ? OR ? IS NULL) " +
                "AND (create_time <= ? OR ? IS NULL) " +
                "ORDER BY create_time DESC";

        Object[] args = new Object[]{
                startTime, endTime
        };
        List<Article> articles=jdbcTemplate.query(sql,args,articleRowMapper);
        return articles.isEmpty()?null:articles;
    }
    //    (8) 按照文章的标签对文章进行查询：
    @Override
    public List<Article> selectByTag(Integer tagId) {
        if(ObjectUtils.isEmpty(tagId)){
            return null;
        }
        String sql="select a.* from tb_article a join tb_article_tag atg on a.id=atg.article_id where atg.tag_id=? order by create_time desc";
        Object[] args=new Object[]{tagId};
        List<Article> articles=jdbcTemplate.query(sql,args,articleRowMapper);
        return articles.isEmpty()?null:articles;
    }
    //    (9) 按照文章的类别进行查询：
    @Override
    public List<Article> selectByCategory(Integer categoryId) {
        if(ObjectUtils.isEmpty(categoryId)){
            return null;
        }
        String sql="select * from tb_article where category_id=? order by create_time desc";
        Object[] args=new Object[]{categoryId};
        List<Article> articles=jdbcTemplate.query(sql,args,articleRowMapper);
        return articles.isEmpty()?null:articles;
    }
}